Skip Headers

Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Managing Views

The following sections discuss views:

- About Views

- Displaying Views

- Creating a View: Example

- Dropping Views

## About Views

Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data, but instead derive their data from the tables upon which they are based. These tables are referred to as the base tables of the view.

Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view. Views provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

Many important views are in the `SYS` schema. The data dictionary views are called static because they change only when a change is made to the data dictionary, for example, when a new table is created or a user is granted new privileges.

Many data dictionary tables have three corresponding views:

- A `DBA_` view displays all relevant information in the entire database. `DBA_` views are intended only for administrators.

- An `ALL_` view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects through privileges or roles.

- A `USER_` view displays all the information from the schema of the current user. No special privileges are required to query these views.

The columns of the `ALL_`, `DBA_`, and `USER_` views corresponding to a single data dictionary table are usually nearly identical.

The views that monitor ongoing database activity are called dynamic performance views. The names of dynamic performance views, available only to administrators, start with the characters `V$` and are often referred to as the `V$` views.

Complete descriptions of the views in the `SYS` schema are contained in the Oracle Database Reference.

## Displaying Views

You can use Enterprise Manager to display the views in a specified schema. You can also display the view definitions.

To display views:

1. In the Administration page, click Views.

   The Views property page appears.

2. In the Schema box, enter the name of a schema. For example, enter `SYS`. Alternatively, click the flashlight icon adjacent to the Schema box to search for a schema.

3. Leave Object Name blank to search for all views in a schema. Alternatively, enter an object name or click the flashlight icon to search for one.

4. Click Go.

   The views in the `SYS` schema are displayed. These views enable you to look at data dictionary information or ongoing database activity. The views in the `SYS` schema are also the source of information for many of the Oracle Enterprise Manager pages.

5. In the results list, select a view and then click View or click the link in the View Name column to display a view definition.

   The View: view_name page appears.

## Creating a View: Example

The following example creates a view derived from the `hr.employees` table, which is part of the sample schema. The view filters all direct reports for the manager King, whose `employee_id` is 100. In an application scenario, this view adds an additional level of security to the `hr.employees` table while providing a suitable presentation of relevant information for manager King.

To create a view:

1. In the Administration page, click Views.

   The Views property page appears.

2. Click Create.

   The Create View page appears.

3. Enter the following information:

   - In Name, enter `KING_VIEW`.

   - In Schema, enter `HR`.

   - In Query Text, enter the following SQL statement:

         SELECT employee_id, first_name, last_name, email, phone_number, hire_date,
                job_id, salary, commission_pct, manager_id, department_id FROM   hr.employees
         WHERE  manager_id = 100

4. Click OK.

   The Views page appears with a confirmation message. The new view is listed in the results section.

5. Select `KING_VIEW` and then select View Data from the Actions list.

6. Click Go.

   The View Data for View: HR.KING_VIEW page appears. The selected data in the view appears in the Result section.

## Dropping Views

If you no longer need a view, then you can drop it using Enterprise Manager.

In this example, you drop the `HR.SKING_VIEW` view that you created previously.

To drop a view:

1. In the Administration page, click Views.

   The Views page appears.

2. In the Schema box, enter `hr`. In Object Name, enter `SKING_VIEW`.

3. Click Go.

   The results page should display the `SKING_VIEW` view.

4. Select `SKING_VIEW` and click Delete.

   A Confirmation page appears.

5. Click Yes to delete the view.

   The Views page displays a confirmation message if the view is successfully deleted.